package org.openapi.utils;

import org.openapi.common.ApiException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;

import java.io.*;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.*;

/**
 * Excel导入导出工具
 * @auth yepanpan
 */
public class ExcelUtil {

    /**
     * 读取Excel的第一个工作表,返回字符串数据列表
     *
     * @param fields  字段映射，中文名=》字段
     * @param is   文件流
     * @param skip 跳过的行数
     * @return
     */
    public static List<Map<String,Object>> read(Map<String,String> fields, InputStream is, int skip) {
        return read(fields, is, skip, 0);
    }

    /**
     * 读取Excel中指定的工作表,返回字符串数据列表
     *
     * @param fields  字段映射，中文名=》字段
     * @param is    文件流
     * @param skip  跳过的行数
     * @param sheet 工作表序号
     * @return
     */
    public static List<Map<String,Object>> read(Map<String,String> fields, InputStream is, int skip, int sheet) {
        List<Map<String,Object>> list = new ArrayList<>();
        try {
            Workbook wb = WorkbookFactory.create(is);
            Sheet ws = wb.getSheetAt(sheet);
            Row header = ws.getRow(skip-1);
            Map<String, Integer> fieldMap = readField(fields, header);
            for (int i = skip; i < ws.getPhysicalNumberOfRows(); i++) {
                Row wr = ws.getRow(i);
                Object v = readVal(wr.getCell(0));
                if(v == null || StrUtil.isEmpty(v.toString())){
                    throw new ApiException("第"+i+"行数据有误，第一列不能为空");
                }
                Map<String,Object> data = new HashMap<>();
                for(String k : fieldMap.keySet()){
                    data.put(k, readVal(wr.getCell(fieldMap.get(k))));
                }
                list.add(data);
            }
            is.close();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 解析列的对应关系
     * @param fields  字段映射，中文名=》字段
     * @param row
     * @return
     */
    public static Map<String,Integer> readField(Map<String,String> fields, Row row){
        Map<String, Integer> fieldMap = new HashMap<>();
        for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
            Object v = readVal(row.getCell(i));
            if(v == null ||StrUtil.isEmpty(v.toString())){
                continue;
            }

            for(String field:fields.keySet()){
                if(field.equalsIgnoreCase(v.toString())){
                    fieldMap.put(fields.get(field), i);
                }
            }
        }
        return fieldMap;
    }

    /**
     * 生成Excel模板文件 2007以上格式
     *
     * @param fields  字段映射，中文名=》字段
     * @param os 输出流
     * @return XSSFWorkbook
     */
    public static void template(Map<String,String> fields, OutputStream os) {
        List<String> titles = new ArrayList<>();
        for(String f:fields.keySet()){
            titles.add(f);
        }
        XSSFWorkbook book = new XSSFWorkbook();
        XSSFSheet sheet = book.createSheet("数据模板");
        fillHeader(sheet, titles, 0);
        try {
            book.write(os);
            book.close();
            os.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 生成Excel模板文件 2007以上格式
     *
     * @param fields  字段映射，中文名=》字段
     * @param os 输出流
     * @return XSSFWorkbook
     */
    public static void template(Map<String,String> fields, Map<String,Object> dict, OutputStream os) {
        XSSFWorkbook book = new XSSFWorkbook();
        XSSFSheet sheet = book.createSheet("数据模板");

        CellStyle style = sheet.getWorkbook().createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        Font titleFont = sheet.getWorkbook().createFont();
        titleFont.setFontName("Arial");
        titleFont.setFontHeightInPoints((short) 16);
        titleFont.setBold(true);
        style.setFont(titleFont);
        XSSFRow row = sheet.createRow(0);
        int i=0;
        for (String h:fields.keySet()) {
            XSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            cell.setCellValue(h);
            String f = fields.get(h);
            String[] combo = dict.entrySet().stream().filter(e->e.getKey().startsWith(f+":")).map(e->e.getValue().toString()).toArray(String[]::new);
            if(combo == null || combo.length == 0){
                i++;
                continue;
            }
            if (combo.length > 15 || StrUtil.join(combo, ",").length() > 255)
            {
                // 如果下拉数大于15或字符串长度大于255，则使用一个新sheet存储，避免生成的模板下拉值获取不到
                setXSSFValidationWithHidden(sheet, combo, "请选择", 1, 1000, i, i);
            }
            else
            {
                // 提示信息或只能选择不能输入的列内容.
                setPromptOrValidation(sheet, combo, "请选择", 1, 1000, i, i);
            }
            i++;
        }

        try {
            book.write(os);
            book.close();
            os.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 生成Excel文件 2007以上格式
     *
     * @param fields  字段映射，中文名=》字段
     * @param rows 数据
     * @return XSSFWorkbook
     */
    public static XSSFWorkbook exports(Map<String,String> fields, List<Map<String,Object>> rows) {
        List<String> titles = new ArrayList<>();
        Map<Integer, String> fieldMap = new HashMap<>();
        int i = 0;
        for(Map.Entry<String,String> e: fields.entrySet()){
            titles.add(e.getKey());
            fieldMap.put(i++, e.getValue());
        }

        XSSFWorkbook book = new XSSFWorkbook();
        XSSFSheet sheet = book.createSheet("数据");
        //fillTitle(sheet, "导出数据", titles.size());
        fillHeader(sheet, titles, 0);
        //空数据
        if(rows == null || rows.size() == 0){
            return book;
        }

        //填充表格数据
        for(i=0;i< rows.size();i++){
            Map data = rows.get(i);
            XSSFRow row = sheet.createRow(i+1);
            for(int c=0;c<titles.size();c++){
                XSSFCell cell = row.createCell(c);
                writeVal(cell, data.get(fieldMap.get(c)));
            }
        }
        return book;
    }

    /**
     * 导出到输出流
     *
     * @param fields  字段映射，中文名=》字段
     * @param rows 数据
     * @param os        OutputStream 输出流
     */
    public static void exports(Map<String,String> fields, List<Map<String,Object>> rows, OutputStream os) {
        XSSFWorkbook book = exports(fields, rows);
        try {
            book.write(os);
            os.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出到文件
     *
     * @param fields  字段映射，中文名=》字段
     * @param rows 数据
     * @param filePath  String 输出路径
     */
    public static void exports(Map<String,String> fields, List<Map<String,Object>> rows, String filePath) {
        try {
            exports(fields, rows, new FileOutputStream(filePath));
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 设置单元格的内容
     *
     * @param cell
     * @param v
     * @return
     */
    public static void writeVal(Cell cell, Object v) {
        XSSFSheet sheet = (XSSFSheet)cell.getSheet();
        if (v == null) {
            cell.setCellValue("");
        } else if (v instanceof String) {
            if (v != null && !"".equals(v) && v.toString().indexOf("\r\n") != -1) {
                XSSFCellStyle style = sheet.getWorkbook().createCellStyle();
                style.setWrapText(true);
                style.setAlignment(HorizontalAlignment.CENTER);
                style.setVerticalAlignment(VerticalAlignment.CENTER);
                cell.setCellStyle(style);
                cell.setCellValue(new XSSFRichTextString(v.toString()));
            } else {
                cell.setCellValue(v.toString());
            }
        } else if (v instanceof Integer) {
            cell.setCellValue((Integer) v);
        } else if (v instanceof Double) {
            cell.setCellValue((Double) v);
        } else if (v instanceof Float) {
            cell.setCellValue((Float) v);
        } else if (v instanceof Long) {
            cell.setCellValue((Long) v);
        } else if (v instanceof Boolean) {
            cell.setCellValue((Boolean) v);
        } else if (v instanceof Date) {
            DataFormat format = sheet.getWorkbook().createDataFormat();// 日期格式化
            CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
            cellStyle.setDataFormat(format.getFormat("yyyy-MM-dd"));
            cell.setCellStyle(cellStyle);

            cell.setCellValue((Date) v);
        } else if (v instanceof BigDecimal) {
            cell.setCellValue(((BigDecimal) v).doubleValue());
        } else {
            cell.setCellValue(v.toString());
        }
    }

    /**
     * 读取单元格的内容
     *
     * @param cell
     * @return
     */
    public static Object readVal(Cell cell) {
        Object val = "";
        try
        {
            if (StrUtil.isNotNull(cell))
            {
                if (cell.getCellType() == CellType.NUMERIC || cell.getCellType() == CellType.FORMULA)
                {
                    val = cell.getNumericCellValue();
                    if (DateUtil.isCellDateFormatted(cell))
                    {
                        val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
                    }
                    else
                    {
                        if ((Double) val % 1 != 0)
                        {
                            val = new BigDecimal(val.toString());
                        }
                        else
                        {
                            val = new DecimalFormat("0").format(val);
                        }
                    }
                }
                else if (cell.getCellType() == CellType.STRING)
                {
                    val = cell.getStringCellValue();
                }
                else if (cell.getCellType() == CellType.BOOLEAN)
                {
                    val = cell.getBooleanCellValue();
                }
                else if (cell.getCellType() == CellType.ERROR)
                {
                    val = cell.getErrorCellValue();
                }

            }
        }
        catch (Exception e)
        {
            return val;
        }
        return val;
    }

    /**
     * 填充表头数据
     *
     * @param sheet  XSSFSheet 表格对象
     * @param titles List<String> 列标题
     * @param start   List<String[]> 数据
     */
    protected static void fillHeader(XSSFSheet sheet, List<String> titles, int start) {
        XSSFRow row = sheet.createRow(start);
        XSSFCellStyle style = sheet.getWorkbook().createCellStyle();
        style.setFillBackgroundColor(IndexedColors.GREEN.getIndex());
        style.setBorderBottom(BorderStyle.MEDIUM);
        style.setBorderTop(BorderStyle.MEDIUM);
        style.setBorderLeft(BorderStyle.MEDIUM);
        style.setBorderRight(BorderStyle.MEDIUM);
        XSSFFont font = sheet.getWorkbook().createFont();
        font.setBold(true);
        style.setFont(font);
        for (int i = 0; i < titles.size(); i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            cell.setCellValue(titles.get(i));
        }
    }

    /**
     * 填充第一行描述信息
     * @param sheet
     * @param title
     * @param cols
     */
    protected static void fillTitle(XSSFSheet sheet, String title, int cols) {
        XSSFRow row = sheet.createRow(0);
        XSSFCellStyle style = sheet.getWorkbook().createCellStyle();
        style.setFillBackgroundColor(IndexedColors.RED.getIndex());
        for (int i = 0; i < cols; i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            if (i == 0) {
                cell.setCellValue(title);
            }
        }
        if(cols > 1){
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, cols - 1));
        }
    }

    /**
     * 设置某些列的值只能输入预制的数据,显示下拉框（兼容超出一定数量的下拉框）.
     *
     * @param sheet 要设置的sheet.
     * @param textlist 下拉框显示的内容
     * @param promptContent 提示内容
     * @param firstRow 开始行
     * @param endRow 结束行
     * @param firstCol 开始列
     * @param endCol 结束列
     */
    public static void setXSSFValidationWithHidden(Sheet sheet, String[] textlist, String promptContent, int firstRow, int endRow, int firstCol, int endCol)
    {
        Workbook wb = sheet.getWorkbook();
        String hideSheetName = "combo_" + firstCol + "_" + endCol;
        Sheet hideSheet = wb.createSheet(hideSheetName); // 用于存储 下拉菜单数据
        for (int i = 0; i < textlist.length; i++)
        {
            hideSheet.createRow(i).createCell(0).setCellValue(textlist[i]);
        }
        // 创建名称，可被其他单元格引用
        Name name = wb.createName();
        name.setNameName(hideSheetName + "_data");
        name.setRefersToFormula(hideSheetName + "!$A$1:$A$" + textlist.length);
        DataValidationHelper helper = sheet.getDataValidationHelper();
        // 加载下拉列表内容
        DataValidationConstraint constraint = helper.createFormulaListConstraint(hideSheetName + "_data");
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        // 数据有效性对象
        DataValidation dataValidation = helper.createValidation(constraint, regions);
        if (StrUtil.isNotEmpty(promptContent))
        {
            // 如果设置了提示信息则鼠标放上去提示
            dataValidation.createPromptBox("", promptContent);
            dataValidation.setShowPromptBox(true);
        }
        // 处理Excel兼容性问题
        if (dataValidation instanceof XSSFDataValidation)
        {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        }
        else
        {
            dataValidation.setSuppressDropDownArrow(false);
        }

        sheet.addValidationData(dataValidation);
        // 设置hiddenSheet隐藏
        wb.setSheetHidden(wb.getSheetIndex(hideSheet), true);
    }

    /**
     * 设置 POI XSSFSheet 单元格提示或选择框
     *
     * @param sheet 表单
     * @param textlist 下拉框显示的内容
     * @param promptContent 提示内容
     * @param firstRow 开始行
     * @param endRow 结束行
     * @param firstCol 开始列
     * @param endCol 结束列
     */
    public static void setPromptOrValidation(Sheet sheet, String[] textlist, String promptContent, int firstRow, int endRow,
                                      int firstCol, int endCol)
    {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = textlist.length > 0 ? helper.createExplicitListConstraint(textlist) : helper.createCustomConstraint("DD1");
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        DataValidation dataValidation = helper.createValidation(constraint, regions);
        if (StrUtil.isNotEmpty(promptContent))
        {
            // 如果设置了提示信息则鼠标放上去提示
            dataValidation.createPromptBox("", promptContent);
            dataValidation.setShowPromptBox(true);
        }
        // 处理Excel兼容性问题
        if (dataValidation instanceof XSSFDataValidation)
        {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        }
        else
        {
            dataValidation.setSuppressDropDownArrow(false);
        }
        sheet.addValidationData(dataValidation);
    }
}
